package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.AucCarInfoDto;
import com.tianqu.tms.dto.BaseCarInfoDto;

public class AucCarInfoDaoImp  extends ViewListDao  implements AucCarInfoDao {
// 竞价管理修正 Start
// 序号,车辆ID,车辆名称,车牌号码,车架号,最高出价(元),竞价开始时间,竞价状态	
	protected final String[] sortKeyList = new String[]{"ID","SERIAL","CARNAME","LICENSEPLATE","VIN","NOWPRICE","STARTTIME","BIDSTATUS"};//会员列表
// 竞价管理修正 End

	/**
	 * 分页构造方法
	 * @param pageNo
	 * @param maxIndex
	 * @param sortKey
	 * @param asc
	 * @throws TmsDaoException
	 */
	protected AucCarInfoDaoImp(int pageNo, int maxIndex, int sortKey, 
			boolean asc) throws TmsDaoException {
		super(pageNo, maxIndex, sortKey, asc);
	}
	
	public AucCarInfoDaoImp() throws TmsDaoException {
		super();
	
	}

	/**
	 * 根据vin获取车辆信息
	 */
	@Override
	public BaseCarInfoDto getAucCar(String Vin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		BaseCarInfoDto aucCarInfoDto = new BaseCarInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SERIAL, ");
		sql.append(" VIN, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" MODELCODE, ");
		sql.append(" VENDORCODE, ");
		sql.append(" REGISTERCARDNO, ");
		sql.append(" ENGINE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" JQX, ");
		sql.append(" SENDCARDDATE, ");
		sql.append(" COLOR, ");
		sql.append(" DISPLACEMENT, ");
		sql.append(" GEARBOX, ");
		sql.append(" CARUSE, ");
		sql.append(" CARTYPE, ");
		sql.append(" CARDTYPE, ");
		sql.append(" COUNTRYSTYLE, ");
		sql.append(" FUELTYPE, ");
		sql.append(" LOADQUALITY, ");
		sql.append(" PASSENGER, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" OWNERORGNO, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");
		sql.append(" INSERTTIME,	");
		sql.append(" VENDORID");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_BASE_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" VIN=?");
		System.out.println("sql======"+sql);
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, Vin);
			rs = ps.executeQuery();
			while (rs.next()) {
				aucCarInfoDto.setSerial(rs.getString("SERIAL"));
				aucCarInfoDto.setVin(rs.getString("VIN"));
				aucCarInfoDto.setLicensePlate(rs.getString("LICENSEPLATE"));
				aucCarInfoDto.setModelCode(rs.getString("MODELCODE"));
				aucCarInfoDto.setVendorCode(rs.getString("VENDORCODE"));
				aucCarInfoDto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
				aucCarInfoDto.setEngine(rs.getString("ENGINE"));
				aucCarInfoDto.setRegDate(rs.getString("REGDATE"));
				aucCarInfoDto.setYearCarefulPeriod(rs
						.getString("YEARCAREFULPERIOD"));
				aucCarInfoDto.setJqx(rs.getString("JQX"));
				aucCarInfoDto.setSendCardDate(rs.getString("SENDCARDDATE"));
				aucCarInfoDto.setColor(rs.getInt("COLOR"));
				aucCarInfoDto.setDisplacement(rs.getInt("DISPLACEMENT"));
				aucCarInfoDto.setGearbox(rs.getInt("GEARBOX"));
				aucCarInfoDto.setCarUse(rs.getInt("CARUSE"));
				aucCarInfoDto.setCarType(rs.getInt("CARTYPE"));
				aucCarInfoDto.setCardType(rs.getInt("CARDTYPE"));
				aucCarInfoDto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
				aucCarInfoDto.setFuelType(rs.getInt("FUELTYPE"));
				aucCarInfoDto.setLoadquality(rs.getInt("LOADQUALITY"));
				aucCarInfoDto.setPassenger(rs.getInt("PASSENGER"));
				aucCarInfoDto.setOwnerType(rs.getInt("OWNERTYPE"));
				aucCarInfoDto.setCarOwner(rs.getString("CAROWNER"));
				aucCarInfoDto.setCarOwnerIdCardNo(rs
						.getString("CAROWNERIDCARDNO"));
				aucCarInfoDto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
				aucCarInfoDto.setOwnerPhone(rs.getString("OWNERPHONE"));
				aucCarInfoDto.setOwnerAddress(rs.getString("OWNERADDRESS"));
				aucCarInfoDto.setBrandId(rs.getString("BRANDID"));
				aucCarInfoDto.setCarStyleId(rs.getString("CARSTYLEID"));
				aucCarInfoDto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				aucCarInfoDto.setVendorId(rs.getString("VENDORID"));

			}
			return aucCarInfoDto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

// 竞价管理修正 Start
	/**
	 * 插入竞价车辆信息
	 * 
	 * @param dto
	 * @throws TmsDaoException
	 */
	@Override
	public int insertAucCar(AucCarInfoDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("	INSERT INTO	");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append("  ( ");
		sql.append("	SERIAL,	");
		sql.append("	CAROWNER,	");
		sql.append("	VIN,	");
		sql.append("	LICENSEPLATE,	");
		sql.append("	PRICE,	");
		sql.append("	COLOR,	");
		sql.append("	YEARCAREFULPERIOD,	");
		sql.append("	JQX,	");
		sql.append("	MILEAGE,	");
		sql.append("	CONTACTPERSON,	");
		sql.append("	CONTACTPHONE,	");
		sql.append("	CAROWNERIDCARDNO,	");
		sql.append("	BRANDID,	");
		sql.append("	CARSTYLEID,	");
		sql.append("	VENDORID,	");
		sql.append("	OWNERPHONE,	");
		sql.append("	OWNERADDRESS,	");
		sql.append("	SEX,	");
		sql.append("	BIRTHDAY,	");
		sql.append("	CARNAME,	");
		sql.append("	REGDATE,	");
		sql.append("	OWNERTYPE,	");
		sql.append("	PHOTO_1,	");
		sql.append("	PHOTO_2,	");
		sql.append("	PHOTO_3,	");
		sql.append("	PHOTO_4,	");
		sql.append("	PHOTO_5,	");
		sql.append("	PHOTO_6,	");
		sql.append("	PHOTO_7,	");
		sql.append("	PHOTO_8,	");
		sql.append("	PHOTO_9,	");
		sql.append("	PHOTO_10,	");		
		sql.append("	PHOTO_11,	");
		sql.append("	PHOTO_12,	");
		sql.append("	PHOTO_13,	");
		sql.append("	PHOTO_14,	");
		sql.append("	PHOTO_15,	");
		sql.append("	PHOTO_16,	");
		sql.append("	PHOTO_17,	");
		sql.append("	PHOTO_18,	");
		sql.append("	PHOTO_19,	");
		sql.append("	PHOTO_20,	");
		sql.append("	PHOTO_21,	");
		sql.append("	PHOTO_22,	"); // 图片从8张增加到20张  modify by mufeiyu
		
		sql.append("	UPDATETIME,	");
		sql.append("	UPDATEUSERID,	");
		sql.append("	INSERTTIME,	");
		sql.append("	INSERTUSERID	");
		sql.append(")VALUES(");
		sql.append("?,?,?,?,?,?,?,?,?,?,?,");
		sql.append("?,?,?,?,?,?,?,?,?,?,?,");
		sql.append("?,?,?,?,?,?,?,?,?,?,?,?,");
		sql.append("?,?,?,?,?,?,?,?,?,?,");
		sql.append("SYSDATE(),?,SYSDATE(),?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getCarOwner());
			ps.setString(3, dto.getVin());
			ps.setString(4, dto.getLicensePlate());
			ps.setInt(5, dto.getPrice());
			ps.setInt(6, dto.getColor());
			ps.setString(7, dto.getYearCarefulPeriod());
			ps.setString(8, dto.getJqx());
			ps.setInt(9, dto.getMileage());
			ps.setString(10, dto.getContactPerson());
			ps.setString(11, dto.getContactPhone());
			ps.setString(12, dto.getCarOwnerIdCardNo());
			ps.setString(13, dto.getBrandId());
			ps.setString(14, dto.getCarStyleId());
			ps.setString(15, dto.getVendorId());
			ps.setString(16, dto.getOwnerPhone());
			ps.setString(17, dto.getOwnerAddress());
			ps.setString(18, dto.getOwnerSex());
			ps.setString(19, dto.getOwnerBirthday());
			ps.setString(20, dto.getCarName());
			ps.setString(21, dto.getRegDate());
			ps.setInt(22, dto.getOwnerType());
			ps.setString(23, dto.getPhoto1());
			ps.setString(24, dto.getPhoto2());
			ps.setString(25, dto.getPhoto3());
			ps.setString(26, dto.getPhoto4());
			ps.setString(27, dto.getPhoto5());
			ps.setString(28, dto.getPhoto6());
			ps.setString(29, dto.getPhoto7());
			ps.setString(30, dto.getPhoto8());
			ps.setString(31, dto.getPhoto9());
			ps.setString(32, dto.getPhoto10());
			ps.setString(33, dto.getPhoto11());
			ps.setString(34, dto.getPhoto12());
			ps.setString(35, dto.getPhoto13());
			ps.setString(36, dto.getPhoto14());
			ps.setString(37, dto.getPhoto15());
			ps.setString(38, dto.getPhoto16());
			ps.setString(39, dto.getPhoto17());
			ps.setString(40, dto.getPhoto18());
			ps.setString(41, dto.getPhoto19());
			ps.setString(42, dto.getPhoto20());
			ps.setString(43, dto.getPhoto21());
			ps.setString(44, dto.getPhoto22()); // 图片从8张增加到20张  modify by mufeiyu
			
			ps.setInt(45, dto.getUpdateUser());
			ps.setInt(46, dto.getUpdateUser());
			
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			rs.next();
			return rs.getInt(1);
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 获得车辆查询列表
	 */
	@Override
	public List<AucCarInfoDto> getaucCarList(String licensePlate, String vin, String startTime)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AucCarInfoDto> list = new ArrayList<AucCarInfoDto>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" C.CARNAME, ");
		sql.append(" C.SERIAL AS SERIAL , ");
		sql.append(" C.LICENSEPLATE AS LICENSEPLATE,   ");
		sql.append(" C.VIN AS VIN,   ");
		sql.append(" M.FINALEXPETPRICES AS NOWPRICE,   ");
		sql.append(" M.STARTTIME AS STARTTIME,   ");
		sql.append(" M.BIDSTATUS AS BIDSTATUS,   ");
		sql.append(" M.ID AS ID   ");
		sql.append(" FROM   ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append(" AS C ,  ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" AS M   ");
		sql.append("  WHERE   ");
		sql.append(" C.SERIAL=M.SERIAL   ");
		System.out.println("@@@@@" + sql);
		if (!StringUtil.isNullOrBlank(licensePlate)) {
			sql.append(" AND SUBSTRING(C.LICENSEPLATE,3,7)='" + licensePlate + "' ");
		}
		if (!StringUtil.isNullOrBlank(vin)) {
			sql.append(" AND SUBSTRING(C.VIN,LENGTH(C.VIN)-5,LENGTH(C.VIN))='" + vin + "' ");
		}
		if (!StringUtil.isNullOrBlank(startTime)) {
			sql.append(" AND SUBSTRING(M.STARTTIME,1,10)<='" + startTime + "' ");
		}
		else {
			sql.append(" AND SUBSTRING(M.STARTTIME,1,10)<SUBSTRING(SYSDATE(),1,10) ");
		}
		//排序方法
		sql.append(getOrderBySql(sortKeyList));
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));
			rs = ps.executeQuery();
			while (rs.next()) {
				AucCarInfoDto dto = new AucCarInfoDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setVin(rs.getString("VIN"));
				dto.setNowPrice(rs.getInt("NOWPRICE"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				list.add(dto);
			}
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
// 竞价管理修正 End

     /**
      * 根据车辆ID获得车辆基本信息
      */
	@Override
	public AucCarInfoDto getaucCarBase(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucCarInfoDto dto = new AucCarInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" * ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		System.out.println("sqltm======"+sql);
		try {
		ps=conn.prepareStatement(sql.toString());
		ps.setString(1, serial);
		rs=ps.executeQuery();
		while (rs.next()) {
			dto.setId(rs.getInt("ID"));
			dto.setSerial(rs.getString("SERIAL"));
			dto.setCarName(rs.getString("CARNAME"));
			dto.setVin(rs.getString("VIN"));
			dto.setLicensePlate(rs.getString("LICENSEPLATE"));
			dto.setPrice(rs.getInt("PRICE"));
			dto.setColor(rs.getInt("COLOR"));
			dto.setRegDate(rs.getString("REGDATE"));
			dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
			dto.setJqx(rs.getString("JQX"));
			dto.setMileage(rs.getInt("MILEAGE"));
			dto.setContactPerson(rs.getString("CONTACTPERSON"));
			dto.setContactPhone(rs.getString("CONTACTPHONE"));
			dto.setCarOwner(rs.getString("CAROWNER"));	
			dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
			dto.setVendorId(rs.getString("VENDORID"));
			dto.setBrandId(rs.getString("BRANDID"));
			dto.setCarStyleId(rs.getString("CARSTYLEID"));
			dto.setOwnerType(rs.getInt("OWNERTYPE"));	
			dto.setOwnerPhone(rs.getString("OWNERPHONE"));	
			dto.setOwnerAddress(rs.getString("OWNERADDRESS"));	
			dto.setVendorId(rs.getString("VENDORID"));
			dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
			dto.setMileage(rs.getInt("MILEAGE"));
			dto.setPrice(rs.getInt("PRICE"));
			dto.setOwnerSex(rs.getString("SEX"));
			dto.setOwnerBirthday(rs.getString("BIRTHDAY"));
			dto.setPhoto1(rs.getString("PHOTO_1"));
			dto.setPhoto2(rs.getString("PHOTO_2"));
			dto.setPhoto3(rs.getString("PHOTO_3"));
			dto.setPhoto4(rs.getString("PHOTO_4"));
			dto.setPhoto5(rs.getString("PHOTO_5"));
			dto.setPhoto6(rs.getString("PHOTO_6"));
			dto.setPhoto7(rs.getString("PHOTO_7"));
			dto.setPhoto8(rs.getString("PHOTO_8"));
			dto.setPhoto9(rs.getString("PHOTO_9"));
			dto.setPhoto10(rs.getString("PHOTO_10"));
			dto.setPhoto11(rs.getString("PHOTO_11"));
			dto.setPhoto12(rs.getString("PHOTO_12"));
			dto.setPhoto13(rs.getString("PHOTO_13"));
			dto.setPhoto14(rs.getString("PHOTO_14"));
			dto.setPhoto15(rs.getString("PHOTO_15"));
			dto.setPhoto16(rs.getString("PHOTO_16"));
			dto.setPhoto17(rs.getString("PHOTO_17"));
			dto.setPhoto18(rs.getString("PHOTO_18"));
			dto.setPhoto19(rs.getString("PHOTO_19"));
			dto.setPhoto20(rs.getString("PHOTO_20"));
			dto.setPhoto21(rs.getString("PHOTO_21"));
			dto.setPhoto22(rs.getString("PHOTO_22")); // 图片从8张增加到20张  modify by mufeiyu
		}
		
		return dto;
		
	} catch (SQLException e) {
		e.printStackTrace();
		throw new TmsDaoException(e.toString());

	} finally {
		super.close(rs, ps);
	}
	}


	/**
	 * 
	 * 根据车辆ID查询车辆基本信息
	 */
	@Override
	public AucCarInfoDto findAucCarInfoBySerial(String serial)
			throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucCarInfoDto aucCarInfoDto = new AucCarInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" SERIAL,CARNAME,");
		sql.append(" VIN, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" JQX, ");
		sql.append(" COLOR, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");//mileage
		sql.append(" MILEAGE, ");
		sql.append(" INSERTTIME,	");
		sql.append(" CONTACTPERSON, ");
		sql.append(" CONTACTPHONE, ");
		sql.append(" PRICE, ");
		sql.append(" SEX, ");
		sql.append(" BIRTHDAY ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, serial);
			
			rs = ps.executeQuery();
			while (rs.next()) {
				aucCarInfoDto.setSerial(rs.getString("SERIAL"));
				aucCarInfoDto.setCarName(rs.getString("CARNAME"));
				aucCarInfoDto.setVin(rs.getString("VIN"));
				aucCarInfoDto.setLicensePlate(rs.getString("LICENSEPLATE"));
				aucCarInfoDto.setRegDate(rs.getString("REGDATE"));
				aucCarInfoDto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
				aucCarInfoDto.setJqx(rs.getString("JQX"));
				aucCarInfoDto.setColor(rs.getInt("COLOR"));
				aucCarInfoDto.setOwnerType(rs.getInt("OWNERTYPE"));
				aucCarInfoDto.setCarOwner(rs.getString("CAROWNER"));
				aucCarInfoDto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
				aucCarInfoDto.setOwnerPhone(rs.getString("OWNERPHONE"));
				aucCarInfoDto.setOwnerAddress(rs.getString("OWNERADDRESS"));
				aucCarInfoDto.setBrandId(rs.getString("BRANDID"));
				aucCarInfoDto.setCarStyleId(rs.getString("CARSTYLEID"));
				aucCarInfoDto.setInsertTime(rs.getTimestamp("INSERTTIME")); 
				aucCarInfoDto.setMileage(rs.getInt("MILEAGE"));
				aucCarInfoDto.setPrice(rs.getInt("PRICE"));
				aucCarInfoDto.setContactPerson(rs.getString("CONTACTPERSON"));
				aucCarInfoDto.setContactPhone(rs.getString("CONTACTPHONE"));
				aucCarInfoDto.setOwnerSex(rs.getString("SEX"));
				aucCarInfoDto.setOwnerBirthday(rs.getString("BIRTHDAY"));

			}
			return aucCarInfoDto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

	}

// 竞价管理修正 Start
	
	/**
	 * 更新车辆信息
	 */
	@Override
	public void updateCar(AucCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("	UPDATE 	");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append("  SET ");
		sql.append("	CAROWNER=?,	");
		sql.append("	VIN=?,	");
		sql.append("	LICENSEPLATE=?,	");
		sql.append("	PRICE=?,	");
		sql.append("	COLOR=?,	");
		sql.append("	REGDATE=?,	");
		sql.append("	YEARCAREFULPERIOD=?,	");
		sql.append("	JQX=?,	");
		sql.append("	MILEAGE=?,	");
		sql.append("	CONTACTPERSON=?,	");
		sql.append("	CONTACTPHONE=?,	");
		sql.append("	CAROWNERIDCARDNO=?,	");
		sql.append("	BRANDID=?,	");
		sql.append("	CARSTYLEID=?,	");
		sql.append("	VENDORID=?,	");
		sql.append("	OWNERPHONE=?,	");
		sql.append("	OWNERADDRESS=?,	");
		sql.append("	SEX=?,	");
		sql.append("	BIRTHDAY=?,	");
		sql.append("	CARNAME=?,	");	
		sql.append("	OWNERTYPE=?,	");
		sql.append("	PHOTO_1=?,	");
		sql.append("	PHOTO_2=?,	");
		sql.append("	PHOTO_3=?,	");
		sql.append("	PHOTO_4=?,	");
		sql.append("	PHOTO_5=?,	");
		sql.append("	PHOTO_6=?,	");
		sql.append("	PHOTO_7=?,	");
		sql.append("	PHOTO_8=?,	");
		sql.append("	PHOTO_9=?,	");
		sql.append("	PHOTO_10=?,	");
		sql.append("	PHOTO_11=?,	");	
		sql.append("	PHOTO_12=?,	");	
		sql.append("	PHOTO_13=?,	");	
		sql.append("	PHOTO_14=?,	");	
		sql.append("	PHOTO_15=?,	");	
		sql.append("	PHOTO_16=?,	");	
		sql.append("	PHOTO_17=?,	");	
		sql.append("	PHOTO_18=?,	");	
		sql.append("	PHOTO_19=?,	");	
		sql.append("	PHOTO_20=?,	");	
		sql.append("	PHOTO_21=?,	");
		sql.append("	PHOTO_22=?,	"); // 图片从8张增加到20张  modify by mufeiyu
		
		sql.append("	UPDATETIME=SYSDATE(),	");
		sql.append("	UPDATEUSERID=?	");
		
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getCarOwner());
			ps.setString(2, dto.getVin());
			ps.setString(3, dto.getLicensePlate());
			ps.setInt(4, dto.getPrice());
			ps.setInt(5, dto.getColor());
			ps.setString(6, dto.getRegDate());
			ps.setString(7, dto.getYearCarefulPeriod());
			ps.setString(8, dto.getJqx());
			ps.setInt(9, dto.getMileage());
			ps.setString(10, dto.getContactPerson());
			ps.setString(11, dto.getContactPhone());
			ps.setString(12, dto.getCarOwnerIdCardNo());
			ps.setString(13, dto.getBrandId());
			ps.setString(14, dto.getCarStyleId());
			ps.setString(15, dto.getVendorId());
			ps.setString(16, dto.getOwnerPhone());
			ps.setString(17, dto.getOwnerAddress());
			ps.setString(18, dto.getOwnerSex());
			ps.setString(19, dto.getOwnerBirthday());
			ps.setString(20, dto.getCarName());
			ps.setInt(21, dto.getOwnerType());
			ps.setString(22, dto.getPhoto1());
			ps.setString(23, dto.getPhoto2());
			ps.setString(24, dto.getPhoto3());
			ps.setString(25, dto.getPhoto4());
			ps.setString(26, dto.getPhoto5());
			ps.setString(27, dto.getPhoto6());
			ps.setString(28, dto.getPhoto7());
			ps.setString(29, dto.getPhoto8());
			ps.setString(30, dto.getPhoto9());
			ps.setString(31, dto.getPhoto10());
			ps.setString(32, dto.getPhoto11());
			ps.setString(33, dto.getPhoto12());
			ps.setString(34, dto.getPhoto13());
			ps.setString(35, dto.getPhoto14());
			ps.setString(36, dto.getPhoto15());
			ps.setString(37, dto.getPhoto16());
			ps.setString(38, dto.getPhoto17());
			ps.setString(39, dto.getPhoto18());
			ps.setString(40, dto.getPhoto19());
			ps.setString(41, dto.getPhoto20());
			ps.setString(42, dto.getPhoto21());
			ps.setString(43, dto.getPhoto22()); // 图片从8张增加到20张  modify by mufeiyu
			
			ps.setInt(44, dto.getUpdateUser());
			ps.setString(45, dto.getSerial());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
// 竞价管理修正 End

	@Override
	public void updateMeliageBySerial(String serial, int mm)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("	UPDATE 	");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append("  SET 					");
		sql.append("  MILEAGE="+mm+",		");
		sql.append("  UPDATETIME=SYSDATE()	");
		sql.append("  WHERE 				");
		sql.append("  SERIAL="+serial+"  	");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
	}
  
	/**
   *如果查询结果集： 真/更新  假/返回
   */
	public boolean updateStatus(String serial,Timestamp updateTime)throws TmsDaoException{
		boolean bean = false;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("		SELECT *	");
		sql.append("   FROM   ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL="+"'"+serial+"'");
		sql.append(" AND ");
		sql.append(" UPDATETIME="+"'"+updateTime+"'");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs =ps.executeQuery();
		
			while (rs.next()) {
				bean=true;
			}
			return bean;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
// 竞价管理修正 Start
	/**
	 * 根据vin获取车辆信息
	 */
	@Override
	public BaseCarInfoDto getBaseCarFromAucCarInfo(String Vin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		BaseCarInfoDto aucCarInfoDto = new BaseCarInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SERIAL, ");
		sql.append(" VIN, ");
		sql.append(" LICENSEPLATE, ");
		sql.append(" REGDATE, ");
		sql.append(" YEARCAREFULPERIOD, ");
		sql.append(" JQX, ");
		sql.append(" COLOR, ");
		sql.append(" OWNERTYPE, ");
		sql.append(" CAROWNER, ");
		sql.append(" CAROWNERIDCARDNO, ");
		sql.append(" OWNERPHONE, ");
		sql.append(" OWNERADDRESS, ");
		sql.append(" BRANDID, ");
		sql.append(" CARSTYLEID, ");
		sql.append(" INSERTTIME,	");
		sql.append(" VENDORID, ");
		sql.append(" PRICE, ");
		sql.append(" MILEAGE, ");
		sql.append(" CONTACTPERSON, ");
		sql.append(" CONTACTPHONE, ");
		sql.append(" SEX, ");
		sql.append(" BIRTHDAY, ");
		sql.append(" PHOTO_1, ");
		sql.append(" PHOTO_2, ");
		sql.append(" PHOTO_3, ");
		sql.append(" PHOTO_4, ");
		sql.append(" PHOTO_5, ");
		sql.append(" PHOTO_6, ");
		sql.append(" PHOTO_7, ");
		sql.append(" PHOTO_8, ");
		sql.append(" PHOTO_9, ");
		sql.append(" PHOTO_10, ");
		sql.append(" PHOTO_11, ");
		sql.append(" PHOTO_12, ");
		sql.append(" PHOTO_13, ");
		sql.append(" PHOTO_14, ");
		sql.append(" PHOTO_15, ");
		sql.append(" PHOTO_16, ");
		sql.append(" PHOTO_17, ");
		sql.append(" PHOTO_18, ");
		sql.append(" PHOTO_19, ");
		sql.append(" PHOTO_20, ");
		sql.append(" PHOTO_21, ");
		sql.append(" PHOTO_22 "); // 图片从8张增加到20张  modify by mufeiyu
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append(" WHERE ");
		sql.append(" VIN=?");
		System.out.println("sql======"+sql);
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, Vin);
			rs = ps.executeQuery();
			while (rs.next()) {
				aucCarInfoDto.setSerial(rs.getString("SERIAL"));
				aucCarInfoDto.setLicensePlate(rs.getString("LICENSEPLATE"));
				aucCarInfoDto.setRegDate(rs.getString("REGDATE"));
				aucCarInfoDto.setYearCarefulPeriod(rs
						.getString("YEARCAREFULPERIOD"));
				aucCarInfoDto.setJqx(rs.getString("JQX"));
				aucCarInfoDto.setColor(rs.getInt("COLOR"));
				aucCarInfoDto.setOwnerType(rs.getInt("OWNERTYPE"));
				aucCarInfoDto.setCarOwner(rs.getString("CAROWNER"));
				aucCarInfoDto.setCarOwnerIdCardNo(rs
						.getString("CAROWNERIDCARDNO"));
				aucCarInfoDto.setOwnerPhone(rs.getString("OWNERPHONE"));
				aucCarInfoDto.setOwnerAddress(rs.getString("OWNERADDRESS"));
				aucCarInfoDto.setBrandId(rs.getString("BRANDID"));
				aucCarInfoDto.setCarStyleId(rs.getString("CARSTYLEID"));
				aucCarInfoDto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				aucCarInfoDto.setVendorId(rs.getString("VENDORID"));
				aucCarInfoDto.setMileage(rs.getInt("MILEAGE"));
				aucCarInfoDto.setPrice(rs.getInt("PRICE"));
				aucCarInfoDto.setContactPerson(rs.getString("CONTACTPERSON"));
				aucCarInfoDto.setContactPhone(rs.getString("CONTACTPHONE"));
				aucCarInfoDto.setOwnerSex(rs.getString("SEX"));
				aucCarInfoDto.setOwnerBirthday(rs.getString("BIRTHDAY"));
				aucCarInfoDto.setPhoto1(rs.getString("PHOTO_1"));
				aucCarInfoDto.setPhoto2(rs.getString("PHOTO_2"));
				aucCarInfoDto.setPhoto3(rs.getString("PHOTO_3"));
				aucCarInfoDto.setPhoto4(rs.getString("PHOTO_4"));
				aucCarInfoDto.setPhoto5(rs.getString("PHOTO_5"));
				aucCarInfoDto.setPhoto6(rs.getString("PHOTO_6"));
				aucCarInfoDto.setPhoto7(rs.getString("PHOTO_7"));
				aucCarInfoDto.setPhoto8(rs.getString("PHOTO_8"));
				aucCarInfoDto.setPhoto9(rs.getString("PHOTO_9"));
				aucCarInfoDto.setPhoto10(rs.getString("PHOTO_10"));
				aucCarInfoDto.setPhoto11(rs.getString("PHOTO_11"));
				aucCarInfoDto.setPhoto12(rs.getString("PHOTO_12"));
				aucCarInfoDto.setPhoto13(rs.getString("PHOTO_13"));
				aucCarInfoDto.setPhoto14(rs.getString("PHOTO_14"));
				aucCarInfoDto.setPhoto15(rs.getString("PHOTO_15"));
				aucCarInfoDto.setPhoto16(rs.getString("PHOTO_16"));
				aucCarInfoDto.setPhoto17(rs.getString("PHOTO_17"));
				aucCarInfoDto.setPhoto18(rs.getString("PHOTO_18"));
				aucCarInfoDto.setPhoto19(rs.getString("PHOTO_19"));
				aucCarInfoDto.setPhoto20(rs.getString("PHOTO_20"));
				aucCarInfoDto.setPhoto21(rs.getString("PHOTO_21"));
				aucCarInfoDto.setPhoto22(rs.getString("PHOTO_22")); // 图片从8张增加到20张  modify by mufeiyu
			}
			aucCarInfoDto.setVin(Vin);
			return aucCarInfoDto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
// 竞价管理修正 End
}